1. Field of Technology
The present application relates generally to database reports. More particularly, the present application is directed to a database system and method for predictive database report caching.
2. Brief Discussion of Related Art
To better understand the behavior of networks, users (e.g., network administrators, customers, as well as other users) access reports related to the performance of their networks. Certain web portals provide automated tools that analyze network performance to aid the users in avoiding potential problems, optimizing resources and planning further operations from a centralized location.
Users of such web portal access reports that perform requests (e.g., SQL requests) against one or more databases, such as Oracle databases. Without a cache, each similar request from the same user (or another user) can lead to a duplicate set of SQL queries, requiring large amounts of processing power and time. Caching methods exist to mitigate processing and time expenditures. With existing caching methods, all reports are stored (cached) in a cache for a period of time.
Caching has several drawbacks: size of the cache, number of reports capable of being cached in the cache, and decreasing access performance to reports in the cache as the number of cached reports increases. Although an existing solution that clears the cache frequently alleviates these drawbacks, this solution is nonetheless problematic because the cache is cleared of all reports and then filled up again without an intelligent mechanism, thereby wasting processing power and the time to process SQL queries of reports that may be required after the cache is cleared.
A company named InfoVista™ has developed a web portal solution called VistaPortal that uses a distributed database system called VistaMart, which is an Oracle database system. VistaPortal is used by customers to display data maintained in the VistaMart database system. VistaPortal uses a cache to reduce the time required to display report data of SQL requests addressed to the VistaMart database system.
Specifically, the cache maintains reports to be displayed by VistaPortal for a pre-defined set of VistaPortal user IDs. The cache can be generated on a regular basis using an InfoVista script called “reports.sh” on Unix platforms or “reports.bat” on Windows platforms. The cache also maintains viewed reports until the cache is cleared manually, or periodically via a “crontab” script.
VistaPortal uses the following mechanism to manage its cache. The reports.sh (or reports.bat) script runs the SQL queries against the VistaMart database to build the required reports in the cache of the VistalPortal. The VistaPortal cache includes operating system (OS) files located on VistaPortal that are accessible by a VistaPortal application.
VistaPortal retrieves the reports from the cache when they are requested by the user via the user's VistaPortal ID. If the report is not present in the cache, VistaPortal runs the required SQL queries against the VistaMart database to build the required report in the cache. Running SQL queries from the VistaPortal against the VistaMart database may take a long time and may degrade the VistaPortal user experience.
Users often notice significant delays in displaying reports, especially those requiring complex SQL requests. Further, as different users perform simultaneous requests to the VistaMart database, a user that issues complex SQL requests may significantly degrade the performance of other users. Therefore, improvements in performance for displaying reports would be desirable.